﻿using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using Lottery.DBUtility;
using Lottery.Entity;
using System.Data;
using System;
using System.Text;

namespace Lottery.DataAccess
{
    public class LotteryFootballSingleGameWDLController
    {
        private const string SELECT_ALLFOOTBALLSINGLEGAMEWDL = "SELECT * FROM LotteryFootballSingleGameWDL ";
        private const string CONDITION_BYISSUEIDANDTEAMS = " WHERE {0}=@{0} AND {1}=@{1} AND {2}=@{2}";
        private readonly string INSERT = string.Format("INSERT INTO LotteryFootballSingleGameWDL({0},{1},{2},{3},{4},{5},{6},{7},{8},{9},{10},{11},{12}) " +
                                                    " VALUES(@{0},@{1},@{2},@{3},@{4},@{5},@{6},@{7},@{8},@{9},@{10},@{11},@{12})",
                                                    PARM_ISSUEID, PARM_IDINISSUE, PARM_MATCHTYPEID, PARM_HOMETEAMID, PARM_VISITINGTEAMID, PARM_LET,
                                                    PARM_HOMESCORE, PARM_VISITINGSCORE, PARM_RESULT, PARM_SP, PARM_TOTALWINNER, PARM_SINGLEBONUSES, PARM_TOTALBONUSES);
        
        private const string PARM_ISSUEID = "IssueID";
        private const string PARM_IDINISSUE = "IDInIssue";
        private const string PARM_MATCHTYPEID = "MatchTypeID";
        private const string PARM_HOMETEAMID = "HomeTeamID";
        private const string PARM_VISITINGTEAMID = "VisitingTeamID";
        private const string PARM_LET = "Let";
        private const string PARM_HOMESCORE = "HomeScore";
        private const string PARM_VISITINGSCORE = "VisitingScore";
        private const string PARM_RESULT = "Result";
        private const string PARM_SP = "SP";
        private const string PARM_TOTALWINNER = "TotalWinner";
        private const string PARM_SINGLEBONUSES = "SingleBonuses";
        private const string PARM_TOTALBONUSES = "TotalBonuses";

        public int Insert(LotteryFootballSingleGameWDL lotteryFootballSingleGameWDL)
        {
            int result = 0;
            if (GetLotteryFootballSingleGameWDLByIssueAndTeams(lotteryFootballSingleGameWDL.IDInIssue, 
                lotteryFootballSingleGameWDL.HomeTeamID, lotteryFootballSingleGameWDL.VisitingTeamID) == null)
            {
                InsertAbsoulte(lotteryFootballSingleGameWDL);
            }
            return result;
        }

        public int InsertAbsoulte(LotteryFootballSingleGameWDL lotteryFootballSingleGameWDL)
        {
            int result = 0;
            SqlParameter[] parms = {
                                       new SqlParameter("@" + PARM_ISSUEID,lotteryFootballSingleGameWDL.IssueID),
                                       new SqlParameter("@" + PARM_IDINISSUE , lotteryFootballSingleGameWDL.IDInIssue),
                                       new SqlParameter("@" + PARM_MATCHTYPEID , lotteryFootballSingleGameWDL.MatchTypeID),
                                       new SqlParameter("@" + PARM_HOMETEAMID , lotteryFootballSingleGameWDL.HomeTeamID),
                                       new SqlParameter("@" + PARM_VISITINGTEAMID , lotteryFootballSingleGameWDL.VisitingTeamID),
                                       new SqlParameter("@" + PARM_LET , lotteryFootballSingleGameWDL.Let),
                                       new SqlParameter("@" + PARM_HOMESCORE , lotteryFootballSingleGameWDL.HomeScore),
                                       new SqlParameter("@" + PARM_VISITINGSCORE , lotteryFootballSingleGameWDL.VisitingScore),
                                       new SqlParameter("@" + PARM_RESULT , lotteryFootballSingleGameWDL.Result),
                                       new SqlParameter("@" + PARM_SP , lotteryFootballSingleGameWDL.SP),
                                       new SqlParameter("@" + PARM_TOTALWINNER , lotteryFootballSingleGameWDL.TotalWinner),
                                       new SqlParameter("@" + PARM_SINGLEBONUSES , lotteryFootballSingleGameWDL.SingleBonuses),
                                       new SqlParameter("@" + PARM_TOTALBONUSES , lotteryFootballSingleGameWDL.TotalBonuses),
                                   };
            result = LotterySQLHelper.ExecuteNonQuery(CommandType.Text, INSERT, parms);
            return result;
        }

        public int Insert(List<LotteryFootballSingleGameWDL> lotteryFootballSingleGameWDLSet)
        {
            int result = 0;
            List<LotteryFootballSingleGameWDL> lotteryFootballSingleGameWDLSetInDB = 
                LotterySQLEntityHelper<LotteryFootballSingleGameWDL>.GetEntitySet(SELECT_ALLFOOTBALLSINGLEGAMEWDL);

            foreach (LotteryFootballSingleGameWDL lotteryFootballSingleGameWDL in lotteryFootballSingleGameWDLSet)
            {
                if (!lotteryFootballSingleGameWDLSetInDB.Select(l => l.IssueID == lotteryFootballSingleGameWDL.IssueID &&
                    l.HomeTeamID == lotteryFootballSingleGameWDL.HomeTeamID &&
                    l.VisitingTeamID == lotteryFootballSingleGameWDL.VisitingTeamID).Any())
                {

                    InsertAbsoulte(lotteryFootballSingleGameWDL);
                }
            }
            return result;
        }

        public LotteryFootballSingleGameWDL GetLotteryFootballSingleGameWDLByIssueAndTeams(int issueID, Guid homeTeamID, Guid visitingTeamID)
        {   
            SqlParameter[] parms = {
                                       new SqlParameter("@" + PARM_ISSUEID , issueID),
                                       new SqlParameter("@" + PARM_HOMETEAMID, homeTeamID),
                                       new SqlParameter("@" + PARM_VISITINGTEAMID, visitingTeamID)
                                   };
            return LotterySQLEntityHelper<LotteryFootballSingleGameWDL>.GetEntity(SELECT_ALLFOOTBALLSINGLEGAMEWDL + CONDITION_BYISSUEIDANDTEAMS, parms);
        }

        public bool NewRecordInsert(ModFootballSingleWDL mod)
        {
            bool result = false;

            try
            {
                SqlParameter[] parms = {
                                           new SqlParameter("@issueNumber", mod.IssueNumber),
                                           new SqlParameter("@idInIssue", mod.IdInIssue),
                                           new SqlParameter("@matchTypeName", mod.MatchTypeName),
                                           new SqlParameter("@homeTeamName", mod.HomeTeamName),
                                           new SqlParameter("@visitingTeamName", mod.VisitingTeamName),
                                           new SqlParameter("@let", mod.Let),
                                           new SqlParameter("@homeScore", mod.HomeScore),
                                           new SqlParameter("@visitingScore", mod.VisitingScore),
                                           new SqlParameter("@result", mod.Result),
                                           new SqlParameter("@sp", mod.SP),
                                           new SqlParameter("@totalWinner", mod.TotalWinner),
                                           new SqlParameter("@singleBonuses", mod.SingleBonuses),
                                           new SqlParameter("@totalBonuses", mod.TotalBonuses)
                                       };
                LotterySQLHelper.ExecuteNonQuery(CommandType.StoredProcedure, "LotteryRecordInsert", parms);
                result = true;
            }
            catch (Exception e)
            {
                throw (e);
            }
            return result;
        }

        public bool NewRecordInsert(List<ModFootballSingleWDL> modFootballSingleWDLSet)
        {
            bool result = false;
            int errorIssueNumber = 0;
            try
            {
                StringBuilder sql = new StringBuilder();

                foreach (ModFootballSingleWDL mod in modFootballSingleWDLSet)
                {
                    errorIssueNumber = mod.IssueNumber;
                    sql.AppendFormat("EXEC LotteryRecordInsert {0},{1},'{2}','{3}','{4}',{5},{6},{7},{8},{9},{10},{11},{12},'{13}';{14}",
                        mod.IssueNumber, mod.IdInIssue, mod.MatchTypeName, mod.HomeTeamName, mod.VisitingTeamName, mod.Let,
                        mod.HomeScore, mod.VisitingScore, mod.Result, mod.SP, mod.TotalWinner, mod.SingleBonuses, mod.TotalBonuses,
                        mod.Html, Environment.NewLine);
                }
                LotterySQLHelper.ExecuteNonQuery(CommandType.Text, sql.ToString());
                result = true;
            }
            catch (Exception e)
            {
                string errorMessage = errorIssueNumber.ToString() + "/" + e.ToString();
                throw new Exception(errorMessage);
            }
            return result;
        }
    }
}
